package com.qtummatrix.dao.daoImpl;


import com.qtummatrix.dao.AttendanceDao;
import com.qtummatrix.entity.Attendance;
import com.qtummatrix.entity.AttendanceBO;
import com.qtummatrix.utils.DBUtil;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

public class AttendanceDaoImpl implements AttendanceDao {

    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    LinkedList<Map> attendanceList = new LinkedList<>();

    /**
     * 显示 各考勤分类的 员工考勤信息
     *
     * @param leaveclass 考勤分类
     * @return
     */
    @Override
    public LinkedList<Map> showAttendanceInfo(String leaveclass) {
        LinkedList<Map> attendanceList = new LinkedList<>();
        //连接数据库
        try {
            connection = DBUtil.getConnection();
            String sql = "SELECT a.id, u.headimg, u.username, d.dempartmentName, a.leavetype, a.starttime, a.endtime," +
                    " a.aduitState FROM user as u\n" +
                    "left join dempartment as d on u.departmentId=d.id\n" +
                    "left join attendance as a on u.id = a.userid\n" +
                    "where a.leaveclass=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, leaveclass);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int attendanceId = resultSet.getInt("a.id");
                String headimg = resultSet.getString("u.headimg");
                String username = resultSet.getString("u.username");
                String departmentName = resultSet.getString("d.dempartmentName");
                String leavetype = resultSet.getString("a.leavetype");
                Date startTime = resultSet.getDate("a.starttime");
                Date endTime = resultSet.getDate("a.endtime");
                String aduitState;
                if (resultSet.getInt("a.aduitState") == 1) {
                    aduitState = "未审核";
                } else {
                    aduitState = "已审核";
                }

                String starttime = DBUtil.dateToString(startTime);
                String endtime = DBUtil.dateToString(endTime);

                Map map = new HashMap();
                map.put("attendanceId", attendanceId);
                map.put("headimg", headimg);
                map.put("username", username);
                map.put("departmentName", departmentName);
                map.put("leavetype", leavetype);
                map.put("starttime", starttime);
                map.put("endtime", endtime);
                map.put("aduitState", aduitState);

                attendanceList.add(map);

            }

           /* //测试 查询结果
            for (Map map : userList) {
                System.out.println(map);
            }*/

            return attendanceList;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }

        return null;
    }

    /**
     * 添加考勤  向考勤表中添加信息
     *
     * @param attendance
     * @return
     */
    @Override
    public int addAttendance(Attendance attendance) {
        try {
            connection = DBUtil.getConnection();
            String sql = "insert into attendance(userid,leaveclass,leavetype,aduitState,starttime,endtime) values " +
                    "(?,?,?,?,?,?)";

            preparedStatement = connection.prepareStatement(sql);
            //员工ID
            int userid = attendance.getUserid();
            preparedStatement.setInt(1, userid);
            //考勤分类
            String leaveclass = attendance.getLeaveclass();
            preparedStatement.setString(2, leaveclass);
            //考勤类型
            String leavetype = attendance.getLeavetype();
            preparedStatement.setString(3, leavetype);
            //审核状态
//            int aduitState = attendance.getAduitState();
//            preparedStatement.setInt(4, aduitState);
            //开始时间
            Date starttime = attendance.getStarttime();
            preparedStatement.setDate(5, starttime);
            //结束时间
            Date endtime = attendance.getEndtime();
            preparedStatement.setDate(6, endtime);


            int i = preparedStatement.executeUpdate();
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }

        return 0;

    }

    /**
     * 通过用户姓名 查找用户id
     *
     * @param username 用户姓名
     * @return
     */
    @Override
    public int search(String username) {
        try {
            connection = DBUtil.getConnection();
            String sql = "select id from user where username= ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                int id = resultSet.getInt("id");

                return id;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return 0;
    }


    /**
     * 编辑考勤信息
     */
    @Override
    public int updateCheck(Attendance attendance) {
        int count = 0;
        try {
            connection = DBUtil.getConnection();
            String sql = "update attendance set leaveclass = ?,leavetype = ?,starttime = ?,endtime = ? where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, attendance.getLeaveclass());
            preparedStatement.setString(2, attendance.getLeavetype());
            preparedStatement.setDate(3, attendance.getStarttime());
            preparedStatement.setDate(4, attendance.getEndtime());
            preparedStatement.setInt(5, attendance.getId());
            count = preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return count;
    }


    /**
     * 删除考勤
     *
     * @param id 考勤id
     * @return
     */
    @Override
    public int delAttendance(int id) {
        try {
            connection = DBUtil.getConnection();
            String sql = "delete from attendance where id = ?";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1, id);
            int i = preparedStatement.executeUpdate();
            return i;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, null);
        }
        return 0;
    }

    /**
     * 查询所有考勤信息
     *
     * @param leaveclass 考勤分类
     * @return
     */
    @Override
    public List<AttendanceBO> searchAllAttendances(String leaveclass) {
        List<AttendanceBO> aboList = new ArrayList<>();
        AttendanceBO abo = null;
        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement(
                    "select u.headimg headimg,a.id  attendanceId,u.id userId, u.username username ,d.dempartmentName " +
                            "departmentName,a.leaveclass leaveType1,a.leavetype leaveType2,a.starttime startTime,a" +
                            ".endtime endTime,a.aduitState aduitState from user u ,attendance a ,dempartment d where " +
                            "u.id = a.userid and u.departmentId=d.id and  leaveclass =?");
            preparedStatement.setString(1, leaveclass);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                abo = new AttendanceBO();
                abo.setUserId(resultSet.getInt("userId"));
                abo.setUsername(resultSet.getString("username"));
                abo.setDepartmentName(resultSet.getString("departmentName"));
                abo.setAduitState(resultSet.getString("aduitState"));
                abo.setLeaveType2(resultSet.getString("leaveType2"));
                abo.setLeaveType1(resultSet.getString("leaveType1"));
                //String start = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(rs.getDate("startTime"));
                Date date1 = resultSet.getDate("startTime");
                Date date2 = resultSet.getDate("endTime");
                abo.setStartTime(DBUtil.dateToString(date1));
                abo.setEndTime(DBUtil.dateToString(date2));
                abo.setHeadimg(resultSet.getString("headimg"));
                abo.setAttendanceId(resultSet.getInt("attendanceId"));
                aboList.add(abo);

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return aboList;
    }


    /**
     * @param leaveclass
     * @return
     */
    @Override
    public List<AttendanceBO> searchAllUserLeave(String leaveclass) {
        AttendanceBO abo = null;
        List<AttendanceBO> aboList = new ArrayList<>();
        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement("select leavetype,COUNT(userid) alluser from attendance " +
                    "where leaveclass=? GROUP BY leavetype");
            preparedStatement.setString(1, leaveclass);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                abo = new AttendanceBO();
                abo.setLeaveType2(resultSet.getString("leavetype"));
                abo.setCount(resultSet.getInt("alluser"));
                aboList.add(abo);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return aboList;
    }


    /**
     * 统计请假的总人数（所有部门加一起）
     *
     * @param leaveclass 考勤分类
     * @return
     */
    @Override
    public Integer countLeaveNumber(String leaveclass) {
        int count = 0;
        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement("select count(id) allcount from attendance where " +
                    "leaveclass = ?");
            preparedStatement.setString(1, leaveclass);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                count = resultSet.getInt("allcount");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return count;
    }


    /**
     * 统计各部门（某个部门）请假、旷工、或迟到早退人数（求所占的比例）
     *
     * @param leaveclass 考勤分类
     * @return
     */
    @Override
    public List<AttendanceBO> countLeaveNumberDocu(String leaveclass) {
        List<AttendanceBO> aboList = new ArrayList<>();
        AttendanceBO abo = null;

        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement(
                    "select d.dempartmentName departmentName , count(d.id) count from dempartment d , user u , " +
                            "attendance a " +
                            "where u.id = a.userid and d.id = u.departmentId and " +
                            "leaveclass=? GROUP BY(d.id)");

            preparedStatement.setString(1, leaveclass);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                abo = new AttendanceBO();
                abo.setDepartmentName(resultSet.getString("departmentName"));
                abo.setCount(resultSet.getInt("count"));
                aboList.add(abo);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return aboList;
    }


    /**
     * 检查用户名是否已存在（已存在返回true；不存在返回false）
     *
     * @param username
     * @return
     */
    @Override
    public Boolean checkUsername(String username) {
        Boolean res = false;

        try {
            connection = DBUtil.getConnection();
            String querySQL = "select id from user where username=?";
            preparedStatement = connection.prepareStatement(querySQL);
            preparedStatement.setString(1, username);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                res = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return res;
    }


    /**
     * 添加员工考勤信息
     *
     * @param userId     员工ID
     * @param leaveclass 考勤分类
     * @param leavetype  考勤类型
     * @param startTime  开始时间
     * @param endTime    结束时间
     * @param aduitState 审核状态
     * @return
     */
    @Override
    public Boolean addAttenDance(int userId, String leaveclass, String leavetype, String startTime, String endTime,
                                 String aduitState) {
        int count = 0;
        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement("insert into attendance value (null,?,?,?,?,?,?)");
            preparedStatement.setInt(1, userId);
            preparedStatement.setString(2, leaveclass);
            preparedStatement.setString(3, leavetype);
            preparedStatement.setString(4, aduitState);
            preparedStatement.setDate(5, DBUtil.StringtoDate(startTime));
            preparedStatement.setDate(6, DBUtil.StringtoDate(endTime));

            count = preparedStatement.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return count > 0 ? true : false;
    }


    /**
     * 根据员工考勤ID查询
     *
     * @param attendanceId
     * @return
     */
    @Override
    public AttendanceBO queryAttendance(int attendanceId) {
        AttendanceBO abo = null;
        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement("select u. username username, d.dempartmentName " +
                    "departmentName,a.leavetype leavetype2 ,a.starttime starttime ,a.endtime endtime ,a.aduitState " +
                    "aduitState from" +
                    " user u , dempartment d , attendance a where " +
                    "u.id=a.userid and u.departmentId=d.id and a.id=?");
            preparedStatement.setInt(1, attendanceId);
            resultSet = preparedStatement.executeQuery();
            //System.out.println("用户名"+rs.getString("username"));
            while (resultSet.next()) {
                abo = new AttendanceBO();
                abo.setUsername(resultSet.getString("username"));
                abo.setDepartmentName(resultSet.getString("departmentName"));
                abo.setLeaveType2(resultSet.getString("leaveType2"));
                Date date1 = resultSet.getDate("startTime");
                Date date2 = resultSet.getDate("endTime");
                abo.setStartTime(DBUtil.dateToString(date1));
                abo.setEndTime(DBUtil.dateToString(date2));
                abo.setAduitState(resultSet.getString("aduitState"));
            }
        } catch (Exception e) {

            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return abo;
    }

    /**
     * 审核修改员工考勤信息
     *
     * @param attendanceId
     * @param leaveType2
     * @param startTime
     * @param endTime
     * @param aduitState
     * @return
     */
    @Override
    public Boolean alterAttendance(int attendanceId, String leaveType2, String startTime, String endTime,
                                   String aduitState) {
        int count = 0;
        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement(
                    "update attendance set leavetype=?,starttime=?,endtime=?,aduitState=? where id=?");
            preparedStatement.setString(1, leaveType2);
            preparedStatement.setDate(2, DBUtil.StringtoDate(startTime));
            preparedStatement.setDate(3, DBUtil.StringtoDate(endTime));
            preparedStatement.setString(4, aduitState);
            preparedStatement.setInt(5, attendanceId);
            count = preparedStatement.executeUpdate();
        } catch (Exception e) {

            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return count > 0 ? true : false;
    }


    /**
     * 删除员工考勤信息
     *
     * @param attendanceId
     * @return
     */
    @Override
    public Boolean deleteAttendance(int attendanceId) {
        int count = 0;

        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement("delete from attendance where id = ?");
            preparedStatement.setInt(1, attendanceId);
            count = preparedStatement.executeUpdate();
        } catch (Exception e) {

            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return count > 0 ? true : false;
    }


    /**
     * 根据条件查询
     *
     * @param username
     * @param startTime
     * @param endTime
     * @param aduitState
     * @return
     */
    @Override
    public List<AttendanceBO> queryAttendances(String username, Date startTime, Date endTime, String aduitState) {
        List<AttendanceBO> aboList = new ArrayList<>();
        AttendanceBO abo = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select a.id attendanceId , u.headimg headimg, u.id userId, u.username username "
                    + ",d.dempartmentName departmentName,a.leaveclass leaveType1,a.leavetype leaveType2,a.starttime " +
                    "startTime,a.endtime endTime, a.aduitState aduitState "
                    + "from user u ,attendance a ,dempartment d where u.id = a.userid and u.departmentId=d.id ";
            if (username != null && username != "") {
                sql += "and u.username = ? ";
            } else {
                sql += " and (1=1 or u.username = ?)";
            }
            if (aduitState != null && aduitState != "") {
                sql += " and a.aduitState =? ";
            } else {
                sql += " and (1=1 or a.aduitState =?) ";
            }
            if (startTime != null) {
                sql += " and a.startTime >=? ";
            } else {
                sql += " and (1=1 or a.startTime >=? )";
                startTime = new Date(0L);
            }
            if (endTime != null) {
                sql += " and a.endTime<=? ";
            } else {
                sql += " and (1=1 or a.endTime <=?)";
                endTime = new Date(0L);
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, aduitState);
            preparedStatement.setDate(3, startTime);
            preparedStatement.setDate(4, endTime);

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                abo = new AttendanceBO();
                abo.setUserId(resultSet.getInt("userId"));
                abo.setUsername(resultSet.getString("username"));
                abo.setDepartmentName(resultSet.getString("departmentName"));
                abo.setAduitState(resultSet.getString("aduitState"));
                abo.setLeaveType2(resultSet.getString("leaveType2"));
                abo.setLeaveType1(resultSet.getString("leaveType1"));
                //String start = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(rs.getDate("startTime"));

                Date date1 = resultSet.getDate("startTime");
                Date date2 = resultSet.getDate("endTime");
                abo.setStartTime(DBUtil.dateToString(date1));
                abo.setEndTime(DBUtil.dateToString(date2));
                abo.setHeadimg(resultSet.getString("headimg"));
                abo.setAttendanceId(resultSet.getInt("attendanceId"));
                aboList.add(abo);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return aboList;
    }
}
